/**
 * 
 */
package com.freecsim.markdown.sql;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;

/**
 * @author Administrator
 *
 */
public class SqlConvertor {
	
	public static String convert(Reader reader){
		DatabaseMarkdownModel databaseModel = new DatabaseMarkdownModel();
		List<DocTable> tables = new ArrayList<DocTable>();
			try {
				BufferedReader br = new BufferedReader(reader);
				List<String> sbs = new ArrayList<String>();
				String line;
				try{
					StringBuilder sb = new StringBuilder();
                    boolean canRead = true;
					while((line = br.readLine()) != null){
						if(line.trim().startsWith("--")){
							continue;
						}

                        //注释开始位置
                        int annStart = line.indexOf("/*");
                        int endStart = line.indexOf("*/");

                        line = line.replaceAll("^\\/\\* .* \\*\\/$","");

                        StringBuilder validSB = new StringBuilder();
                        if(annStart > 0) {
                            validSB.append(line.substring(0, annStart));
                        }

                        if(endStart != -1){
                            validSB.append(line.substring(endStart + 1));
                        }


						if(line.startsWith("/*")){

							continue;
						}

                        if(canRead) {
                            int sepPos = line.indexOf(";");
                            if (sepPos != -1) {
                                String sql = line.substring(0, sepPos + 1);
                                sb.append(sql);
                                sbs.add(sb.toString());
                                sb = new StringBuilder();
                                sb.append(line.substring(sepPos + 1));
                            } else {
                                sb.append(line);
                            }
                        }
					}
				}catch (IOException e) {
					e.printStackTrace();
				}
				
				for(String sqlStatement : sbs){
					Statement statement = CCJSqlParserUtil.parse(sqlStatement);
					if(statement instanceof CreateTable){
						DocTable docTable = parseCreateTable((CreateTable) statement);
						tables.add(docTable);
					}
				}
				
			}catch (JSQLParserException e) {
				e.printStackTrace();
			}
			databaseModel.setDatabaseName("falcon");
			databaseModel.setTables(tables);
			return databaseModel.toMarkdownString();
	}

    /**
     * 过滤到注释
     * @param content 需要过滤的源数据
     * @return
     */
    public static String fileAnnotaion(String content){
        String result = content.replaceAll("^\\/\\* .* \\*\\/$", "");
        result.replaceAll("^\\/\\* .* \n$", "");
        result.replaceAll("^.* \\*/$", "");
        return result;
    }
	
	 private static DocMysqlTable parseCreateTable(CreateTable createTable){
	    	if(createTable == null){
	    		throw new NullPointerException();
	    	}
			List<ColumnDefinition> columnDefList = createTable.getColumnDefinitions();
			Table table = createTable.getTable();
			DocMysqlTable docTable = new DocMysqlTable();
			docTable.setName(filterSpecialChar(table.getName()));
			List<DocColumn> columns = new ArrayList<DocColumn>();
			for(ColumnDefinition colDef : columnDefList){
				DocColumn c = new DocColumn();
				c.setName(filterSpecialChar(colDef.getColumnName()));
				c.setDataType(colDef.getColDataType().toString());
				List<String> specString =  colDef.getColumnSpecStrings();
				StringBuilder specSB = new StringBuilder();
				
				for(String spec : specString){
					specSB.append(spec).append(" ");
				}
				int commontPos = specSB.indexOf("COMMENT");
				if(commontPos != -1){
					String commontStr = specSB.substring(commontPos);
					commontStr = commontStr.replaceAll("COMMENT", "");
					c.setComment(commontStr.trim().replaceAll("'", ""));
					specSB = specSB.delete(commontPos, specSB.length());
				}
				
				int defaultPos = specSB.indexOf("DEFAULT");
				if( defaultPos != -1){
					String defaultString = specSB.substring(defaultPos);
					defaultString = defaultString.replaceAll("DEFAULT", "");
					c.setDefaultValue(defaultString.trim());
					specSB = specSB.delete(defaultPos, specSB.length());
				}
				
				if(specSB.indexOf("NOT NULL") != -1){
					c.setNotNull(true);
					specSB = specSB.delete(specSB.indexOf("NOT NULL"), specSB.length());
				}else if(specSB.indexOf("NULL") != -1){
					c.setNotNull(false);
					specSB = specSB.delete(specSB.indexOf("NULL"), specSB.length());
				}
				columns.add(c);
			}
			
			List list = createTable.getTableOptionsStrings();
			docTable.setDefaultCharset(parseOptionValue("CHARSET", list));
			docTable.setEngine(parseOptionValue("ENGINE", list));
			docTable.setComment(parseOptionValue("COMMENT", list));
			
//			List<Index> indexs = createTable.getIndexes();
//			for(Index index : indexs){
//				System.out.println("index:" + index);
//			}
			docTable.setColumns(columns);
			return docTable;
	}
	
	private static String parseOptionValue(String key, List<?> list){
    	if(list != null){
			if(list.contains(key)){
				int index = list.indexOf(key);
				String value = list.get(index + 2).toString();
				return filterSpecialChar(value);
			}else{
				return null;
			}
		}else{
			return null;
		}
    }
	
	/**
	 * 过滤掉特定字符
	 * @param content
	 * @return
	 */
	private static String filterSpecialChar(String content){
		if(content == null){
			return "";
		}else{
			content = content.replace("'", "");
			return content.replace("`", "");
		}
	}
}
